package br.com.portalecbahia.dao;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import br.com.portalecbahia.model.Match;
import br.com.portalecbahia.util.PortalECBahiaUtil;

public class MatchDAO {

	private Context context;
	private SQLiteDatabase database;
	private DBHelper dbHelper;

	public MatchDAO(Context context) {
		this.context = context;
	}

	public MatchDAO openWritable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getWritableDatabase();
		return this;
	}
	public MatchDAO openReadable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getReadableDatabase();
		return this;
	}
	

	public void close() {
		dbHelper.close();
	}
	

	public List<Match> findAll () {
		this.openReadable();

		List<Match> result = new ArrayList<Match>();		
		Cursor cursor;
	 
		try{
			cursor = database.rawQuery("SELECT ID," +
									   "championshipName, " +
									   "stadimName, " +
									   "stadiumId, " +
									   "awayTeamName, "+
									   "awayTeamUrlImage, " + 
									   "homeTeamName, " +
									   "homeTeamUrlImage, " +
									   "date, " +
									   "dateUTC, " +
									   "homeGoal, " +
									   "awayGoal, " +
									   "finished," +
									   "awayTeamShortName," +
									   "homeTeamShortName, " +
									   "live "+
									   "FROM tbl_match " +
									   "order by date", null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()){
				do{
					result.add(this.bindSQLite(cursor));
				}while (cursor.moveToNext());
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		
		this.close();
		
		return result;
	}
	
	public List<Match> findHome () {
		this.openReadable();

		List<Match> result = new ArrayList<Match>();		
		Cursor cursor;
	 
		try{
			cursor = database.rawQuery("SELECT ID," +
									   "championshipName, " +
									   "stadimName, " +
									   "stadiumId, " +
									   "awayTeamName, "+
									   "awayTeamUrlImage, " + 
									   "homeTeamName, " +
									   "homeTeamUrlImage, " +
									   "date, " +
									   "dateUTC, " +
									   "homeGoal, " +
									   "awayGoal, " +
									   "finished, " +
									   "awayTeamShortName," +
									   "homeTeamShortName, " +
									   "live "+
									   "FROM tbl_match " +
									   "WHERE homeTeamName = 'Bahia' " +
									   "order by date", null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()){
				do{
					result.add(this.bindSQLite(cursor));
				}while (cursor.moveToNext());
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		
		this.close();
		
		return result;
	}
	
	public Match findByPrimaryKey (Match match) {
		this.openReadable();

		Match result = new Match();		
		Cursor cursor;
	 
		try{
			cursor = database.rawQuery("SELECT ID," +
									   "championshipName, " +
									   "stadimName, " +
									   "stadiumId, " +
									   "awayTeamName, "+
									   "awayTeamUrlImage, " + 
									   "homeTeamName, " +
									   "homeTeamUrlImage, " +
									   "date, " +
									   "dateUTC, " +
									   "homeGoal, " +
									   "awayGoal, " +
									   "finished, " +
									   "awayTeamShortName," +
									   "homeTeamShortName, " +
									   "live "+
									   "FROM tbl_match " +
									   "WHERE ID = " +match.getId()+"", null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()){
				
					result = this.bindSQLite(cursor);
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		
		this.close();
		
		return result;
	}
	
	public void insert(JSONArray jsonArray) throws JSONException, ParseException {
		this.openWritable();
		Cursor cursor = null;
		try{
			database.beginTransaction();
			for (int i = 0; i < jsonArray.length(); i++) {
				JSONObject json = jsonArray.getJSONObject(i);
				int flag = (json.getBoolean("finished"))? 1 : 0;
				int live = (json.getBoolean("live"))? 1 : 0;

				cursor = database.rawQuery(" INSERT INTO tbl_match (ID, championshipName,  stadimName, stadiumId, awayTeamName,  awayTeamUrlImage,  homeTeamName,  homeTeamUrlImage,  date,  dateUTC,  homeGoal, awayGoal,  finished, awayTeamShortName, homeTeamShortName, live) " +
							"VALUES (" + json.getLong("id")            +",  " +
									"'"+json.getString("championshipName")        +"', "+
									"'"+json.getString("stadimName")     +"', " +
									""+json.getLong("stadiumID")     +", " +
									"'"+json.getString("awayTeamName")    	   +"', " +
									"'"+json.getString("awayTeamUrlImage") 	       +"', "+
									"'"+json.getString("homeTeamName")    	   +"', " +
									"'"+json.getString("homeTeamUrlImage") 	       +"', "+
									"'"+json.getString("date") 	       +"', "+
									"'"+json.getString("dateUTC") 	       +"', "+
									"" +json.getInt("homeGoal") +", "+
									"" +json.getInt("awayGoal") +", "+
									"" +flag+"," +
									"'"+json.getString("awayTeamShortName")    	   +"', " +
									"'"+json.getString("homeTeamShortName")    	   +"', " +
									"" +live+");", null);
					
					cursor.moveToFirst();
				
			}
		}catch (SQLException e) {
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}finally {
			
			database.setTransactionSuccessful();
			database.endTransaction();
			
		}
		
		this.close();
		
	}
	
	
	
	public void removeAll() {
		this.openWritable();
		Cursor cursor = null;
		try{
			cursor = database.rawQuery("DELETE FROM tbl_match ", null);
			cursor.moveToFirst();
			cursor.close();
		}catch (SQLException e) {
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		this.close();
		
	}


	private Match bindSQLite(Cursor cursor){
		Match match  = new Match();
		SimpleDateFormat dateFormat = new  SimpleDateFormat("yyyy-MM-dd HH:mm");
		for (int i = 0; i < cursor.getColumnCount(); i++){
		
				if("ID".equals(cursor.getColumnName(i)))
					match.setId(cursor.getLong(i));
				else if("championshipName".equals(cursor.getColumnName(i))) 
					match.setChampionshipName(cursor.getString(i));
				else if("stadimName".equals(cursor.getColumnName(i))) 
					match.setStadimName(cursor.getString(i));
				else if("stadiumId".equals(cursor.getColumnName(i))) 
					match.setStadiumId(cursor.getLong(i));
				
				else if("awayTeamName".equals(cursor.getColumnName(i))) 
					match.setAwayTeamName(cursor.getString(i));
				else if("awayTeamShortName".equals(cursor.getColumnName(i))) 
					match.setAwayTeamShortName(cursor.getString(i));
				else if("homeTeamShortName".equals(cursor.getColumnName(i))) 
					match.setHomeTeamShortName(cursor.getString(i));
				else if("awayTeamUrlImage".equals(cursor.getColumnName(i)))
					match.setAwayTeamUrlImage(cursor.getString(i));
				else if("homeTeamName".equals(cursor.getColumnName(i))) 
					match.setHomeTeamName(cursor.getString(i));
				else if("homeTeamUrlImage".equals(cursor.getColumnName(i))) 
					match.setHomeTeamUrlImage(cursor.getString(i));
				else if("date".equals(cursor.getColumnName(i)))
					try {	
						match.setDate(dateFormat.parse(cursor.getString(i)));
					} catch (ParseException e) {
						Log.e(PortalECBahiaUtil.TAG, e.getMessage());
					}
				else if("dateUTC".equals(cursor.getColumnName(i)))
					try {	
						match.setDateUTC(dateFormat.parse(cursor.getString(i)));
					} catch (ParseException e) {
						Log.e(PortalECBahiaUtil.TAG, e.getMessage());
					}
				else if("homeGoal".equals(cursor.getColumnName(i)))
					match.setHomeGoal(cursor.getInt(i));
				else if("awayGoal".equals(cursor.getColumnName(i)))
					match.setAwayGoal(cursor.getInt(i));
				else if("finished".equals(cursor.getColumnName(i)))
					match.setFinished((cursor.getInt(i) == 1)? true : false);
				else if("live".equals(cursor.getColumnName(i)))
					match.setLive((cursor.getInt(i) == 1)? true : false);
			
				
		}
		return match;
	}

	public List<Match> findAway() {
		this.openReadable();

		List<Match> result = new ArrayList<Match>();		
		Cursor cursor;
	 
		try{
			cursor = database.rawQuery("SELECT ID," +
									   "championshipName, " +
									   "stadimName, " +
									   "stadiumId, " +

									   "awayTeamName, "+
									   "awayTeamUrlImage, " + 
									   "homeTeamName, " +
									   "homeTeamUrlImage, " +
									   "date, " +
									   "dateUTC, " +
									   "homeGoal, " +
									   "awayGoal, " +
									   "finished, " +
									   "awayTeamShortName," +
									   "homeTeamShortName, " +
									   "live "+
									   "FROM tbl_match " +
									   "WHERE awayTeamName = 'Bahia' " +
									   "order by date", null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()){
				do{
					result.add(this.bindSQLite(cursor));
				}while (cursor.moveToNext());
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		
		this.close();
		
		return result;
	}

	public void updateScore(int home, int away, Match match) {
			this.openWritable();
			Cursor cursor = null;
			try{
					cursor = database.rawQuery("UPDATE  tbl_match " +
											   "SET homeGoal =" +home+", "+
											   "awayGoal =" +away+" "+
											   "WHERE id ="+match.getId(), null);
					cursor.moveToFirst();
					cursor.close();
					this.close();
			}catch (SQLException e){
				Log.e(PortalECBahiaUtil.TAG, e.toString());
			}
	}
			
}
